https://forcats.tidyverse.org/reference/fct_relevel.html

library(tidyverse)
## -- Attaching packages --------------------------------------- tidyverse 1.3.1 --
## v ggplot2 3.3.5     v purrr   0.3.4
## v tibble  3.1.2     v dplyr   1.0.7
## v tidyr   1.1.3     v stringr 1.4.0
## v readr   1.4.0     v forcats 0.5.1
## -- Conflicts ------------------------------------------ tidyverse_conflicts() --
## x dplyr::filter() masks stats::filter()
## x dplyr::lag()    masks stats::lag()
library(janitor)
## Warning: package 'janitor' was built under R version 4.1.1
## 
## Attaching package: 'janitor'
## The following objects are masked from 'package:stats':
## 
##     chisq.test, fisher.test
library(readxl)
options(scipen = 0)
state <- readxl::read_excel("CSR_Data_State_Sector_Wise.xlsx" , range = "A2:H40") %>% 
  janitor::clean_names() 

new_names <- c("state_ut", "FY14-15","FY15-16","FY16-17","FY17-18","FY18-19","FY19-20", "FY20-21")

state <- state %>% 
  set_names(new_names) %>% 
  pivot_longer(-state_ut, names_to = "financial_year", values_to = "amount_inr_crores") %>% 
  mutate(amount_inr_crores = format(amount_inr_crores, scientific = F, digits = 2),
         amount_inr_crores = as.double(amount_inr_crores))
new_names <- c("sector", "FY14-15","FY15-16","FY16-17","FY17-18","FY18-19","FY19-20", "FY20-21")
sector <- readxl::read_excel("CSR_Data_Development_Sector_Wise.xlsx", range = "A2:H31") %>% 
  clean_names() %>% 
  set_names(new_names) %>% 
  pivot_longer(-sector, names_to = "financial_year", values_to = "amount_inr_crores")

Per FY FY14 to FY21

sector %>% 
  group_by(financial_year) %>% 
  summarize(total_csr_spend_per_fy =  sum(amount_inr_crores)) %>% 
  mutate(total_csr_spend_per_fy = round(total_csr_spend_per_fy, 0))
## # A tibble: 7 x 2
##   financial_year total_csr_spend_per_fy
##   <chr>                           <dbl>
## 1 FY14-15                         10066
## 2 FY15-16                         14517
## 3 FY16-17                         14344
## 4 FY17-18                         17098
## 5 FY18-19                         20150
## 6 FY19-20                         24689
## 7 FY20-21                          8828
state %>% 
  group_by(financial_year) %>% 
  summarize(total_csr_spend_per_fy =  sum(amount_inr_crores)) %>% 
   mutate(total_csr_spend_per_fy = round(total_csr_spend_per_fy, 0))
## # A tibble: 7 x 2
##   financial_year total_csr_spend_per_fy
##   <chr>                           <dbl>
## 1 FY14-15                         10066
## 2 FY15-16                         14517
## 3 FY16-17                         14344
## 4 FY17-18                         17098
## 5 FY18-19                         20150
## 6 FY19-20                         24689
## 7 FY20-21                          8828

Total FY,Average, Max, Min

state %>% 
  group_by(financial_year) %>% 
  summarize(total_csr_spend_per_fy =  sum(amount_inr_crores)) %>% 
  summarize(total_csr_spend = sum(total_csr_spend_per_fy), 
            average_csr = mean(total_csr_spend_per_fy),
            max_csr_fy = max(total_csr_spend_per_fy),
            min_csr_fy = min(total_csr_spend_per_fy))
## # A tibble: 1 x 4
##   total_csr_spend average_csr max_csr_fy min_csr_fy
##             <dbl>       <dbl>      <dbl>      <dbl>
## 1         109692.      15670.     24689.      8828.

Per State total CSR Spent from FY14-15 to FY20-21

g1 <- state %>% 
  filter(amount_inr_crores >= 10) %>% 
  group_by(state_ut) %>% 
  summarize(total_state_inr_crores = sum(amount_inr_crores)) %>% 
  mutate(state_ut = fct_reorder(state_ut, total_state_inr_crores)) %>% 
  ggplot(aes(y = state_ut, x = total_state_inr_crores)) +
  geom_col(fill = "purple") + 
  geom_text(aes(label = round(total_state_inr_crores,0)), size = 2, hjust = 0, fontface = "bold") +
  labs(y = "States/Pan India", 
       x = "Total spend in crores(INR)", 
       title = "Total CSR Spend by States", subtitle = "combined total from FY14-15 to FY20-21", 
       caption = "data: MCA, India, graph:os2137@caa.columbia.edu") 
  
g1

  plotly::ggplotly(g1)

CSR Spend Cumulative FY14-15 to FY20-21: Top 10 States

state %>% 
  group_by(state_ut) %>% 
  summarize(total_state_inr_crores = sum(amount_inr_crores)) %>% 
  arrange(desc(total_state_inr_crores)) %>% 
  head(10) %>% 
  mutate(state_ut = fct_reorder(state_ut, total_state_inr_crores)) %>% 
  ggplot(aes(y = state_ut, x = total_state_inr_crores)) +
  geom_col(fill = "purple") + 
  geom_text(aes(label = round(total_state_inr_crores,0)), size = 2, hjust = 0, fontface = "bold") +
  labs(y = "States/Pan India", 
       x = "Total spend in crores(INR)", 
       title = "Top 10 States by CSR Spend", subtitle = "combined total from FY14-15 to FY20-21", 
       caption = "data: MCA, India, graph:os2137@caa.columbia.edu")

Per State per FY csr spend

g <- state %>%
  filter(amount_inr_crores >= 500) %>% 
  # mutate(financial_year = fct_relevel(
  #   financial_year,
  #   c(
  #     "FY14-15",
  #     "FY15-16",
  #     "FY16-17",
  #     "FY17-18",
  #     "FY18-19",
  #     "FY19-20",
  #     "FY20-21"
  #   )
mutate(financial_year = as.factor(financial_year))%>%
  mutate(financial_year = forcats::fct_rev(financial_year)) %>% 
  mutate(state_ut = fct_reorder(state_ut, amount_inr_crores)) %>%
  ggplot(aes(y = state_ut, x = amount_inr_crores, fill = financial_year)) +
  geom_col() +
  facet_wrap(~financial_year)+
geom_text(aes(label = round(amount_inr_crores,0)), size = 2, hjust = 0, vjust = 0.5) +

scale_fill_discrete(guide=guide_legend(reverse=T)) +
#   reference: https://stackoverflow.com/questions/38425908/reverse-stacking-order-without-affecting-legend-order-in-ggplot2-bar-charts 
# or +  guides(fill = guide_legend(reverse = TRUE)) +
theme(legend.position = "none") +
  labs(x = "Amount in INR Crores", 
       y = "State/Pan India", 
       title = "States with highest CSR Spend from 2014-15 to FY2020-21(INR Crores)",
       subtitle = "States where the total spend was INR 500 crores or more in a FY (1 Crore = 10 Million)", 
       caption = "Data: Min. of Corp. Affair, India")

g

plotly::ggplotly(g)
  # levels(as.factor(state$financial_year))
g2 <- sector %>% 
  group_by(sector) %>% 
  summarize(total_investment_inr_crores = sum(amount_inr_crores, na.rm = T)) %>% 
  mutate(sector =
           fct_reorder(sector, total_investment_inr_crores)) %>%
  mutate(total_investment_inr_crores = round(total_investment_inr_crores, 0)) %>% 
          ggplot(aes(x = total_investment_inr_crores, y = sector)) +
  geom_col(fill = "purple") +
geom_text(aes(label = total_investment_inr_crores), size = 2, hjust = 0)

# plotly::ggplotly(g2)
sector %>% 
  group_by(sector) %>% 
  summarize(total_investment_inr_crores = sum(amount_inr_crores, na.rm = T)) %>% 
  mutate(sector =
           fct_reorder(sector, total_investment_inr_crores)) %>%
  mutate(total_investment_inr_crores = round(total_investment_inr_crores, 0)) %>% 
          ggplot(aes(x = total_investment_inr_crores, y = sector)) +
  geom_col(fill = "purple") +
  
  geom_text(aes(label = total_investment_inr_crores), size = 2, hjust = 0, vjust = 0.5)

## CSR Spend Cumulative FY14-15 to FY20-21: Top 10 Sectors

sector %>% 
  group_by(sector) %>% 
  summarize(total_investment_inr_crores = sum(amount_inr_crores, na.rm = T)) %>% 
  arrange(desc(total_investment_inr_crores)) %>% 
  head(10) %>% 
            
  mutate(sector =
           fct_reorder(sector, total_investment_inr_crores)) %>%
  mutate(total_investment_inr_crores = round(total_investment_inr_crores, 0)) %>% 
          ggplot(aes(x = total_investment_inr_crores, y = sector)) +
  geom_col(fill = "purple") +
  
  geom_text(aes(label = total_investment_inr_crores), size = 2, hjust = 0, vjust = 0.5) +
  labs(y = "Sectors", 
       x = "Total spend in crores(INR)", 
       title = "Top 10 Sectors by CSR Spend", subtitle = "combined total from FY14-15 to FY20-21", 
       caption = "data: Minstry of Corp. Affairs, India, graph:os2137@caa.columbia.edu") 

g <- sector %>% 
  mutate(sector = fct_reorder(sector, amount_inr_crores)) %>% 
  mutate(amount_inr_crores = round(amount_inr_crores, 0)) %>% 
          ggplot(aes(x = amount_inr_crores, y = sector, fill = financial_year)) +
  geom_col() +
  guides(fill = guide_legend(reverse=TRUE))
  
  

plotly::ggplotly(g)
g3 <- sector %>%
  filter(amount_inr_crores >= 500) %>% 
  # mutate(financial_year = fct_relevel(
  #   financial_year,
  #   c(
  #     "FY14-15",
  #     "FY15-16",
  #     "FY16-17",
  #     "FY17-18",
  #     "FY18-19",
  #     "FY19-20",
  #     "FY20-21"
  #   )
mutate(financial_year = as.factor(financial_year))%>%
  mutate(financial_year = forcats::fct_rev(financial_year)) %>% 
  mutate(state_ut = fct_reorder(sector, amount_inr_crores)) %>%
  ggplot(aes(y = sector, x = amount_inr_crores, fill = financial_year)) +
  geom_col() +
  facet_wrap(~financial_year)+
geom_text(aes(label = round(amount_inr_crores,0)), size = 2, hjust = 0, vjust = 0.5) +

scale_fill_discrete(guide=guide_legend(reverse=T)) +
#   reference: https://stackoverflow.com/questions/38425908/reverse-stacking-order-without-affecting-legend-order-in-ggplot2-bar-charts 
# or +  guides(fill = guide_legend(reverse = TRUE)) +
theme(legend.position = "none") +
  labs(x = "Amount in INR Crores", 
       y = "State/Pan India", 
       title = "Sector with highest CSR Spend from 2014-15 to FY2020-21(INR Crores)",
       subtitle = "Sector where the total spend was INR 500 crores or more in a FY (1 Crore = 10 Million)", 
       caption = "Data: Min. of Corp. Affairs, India")

g3

psu_non_psu <- readxl::read_excel("CSR_Data_PSU_Non_PSU_wise.xlsx", range = "A2:O4") %>% 
  clean_names()
names(psu_non_psu)
##  [1] "psu_non_psu"                     "total_company_fy_2014_15"       
##  [3] "amount_spent_fy_2014_15_inr_cr"  "total_company_fy_2015_16"       
##  [5] "amount_spent_fy_2015_16_inr_cr"  "total_company_fy_2016_17"       
##  [7] "amount_spent_fy_2016_17_inr_cr"  "total_company_fy_2017_18"       
##  [9] "amount_spent_fy_2017_18_inr_cr"  "total_company_fy_2018_19"       
## [11] "amount_spent_fy_2018_19_inr_cr"  "total_company_fy_2019_20"       
## [13] "amount_spent_fy_2019_20_inr_cr"  "total_company_fy_2020_2021"     
## [15] "amount_spent_fy_20120_21_inr_cr"
dim(psu_non_psu)
## [1]  2 15
new_names <- c("psu_non_psu", 
               "comp_2014-2015", 
               "spendinrcrores_2014-2015",
               "comp_2015-2016", 
               "spendinrcrores_2015-2016",
               "comp_2016-2017", 
               "spendinrcrores_2016-2017",
               "comp_2017-2018", 
               "spendinrcrores_2017-2018",
               "comp_2018-2019", 
               "spendinrcrores_2018-2019",
               "comp_2019-2020", 
               "spendinrcrores_2019-2020",
               "comp_2020-2021", 
               "spendinrcrores_2020-2021"
               )
psu_non_psu <- psu_non_psu %>% set_names(new_names)
psu_non_psu_1 <- psu_non_psu %>% 
  select(1, 2,4,6,8,10,12,14) %>% 
    pivot_longer(cols = c("comp_2014-2015": "comp_2020-2021"), 
names_to = "company_fy", 
values_to = "number_of_comapanies") %>% 
  separate(company_fy, c("company", "fy"), sep = "_", extra = "merge") 
psu_non_psu_2 <- psu_non_psu %>%  
  select(1, 3, 5, 7, 9, 11, 13, 15) %>% 
 pivot_longer( cols = c("spendinrcrores_2014-2015":"spendinrcrores_2020-2021"), 
names_to = "company_fy", 
values_to = "amount_spent_inr_crores") %>% 
  separate(company_fy, c("company", "fy"), sep = "_", extra = "merge") 
final_psu_non_psu <- psu_non_psu_1 %>% 
  left_join(psu_non_psu_2, by = c("psu_non_psu", "fy")) %>% 
  select(1, 3, 4, 6)
final_psu_non_psu %>% 
  ggplot(aes(x = fy, y = amount_spent_inr_crores, color = psu_non_psu), size = amount_spent_inr_crores)+
  geom_point() +
  geom_line(group = 1)+
  facet_wrap(~psu_non_psu)+
  
    ggrepel::geom_text_repel(aes(label = number_of_comapanies), size = 1.5, color = "blue",  hjust = 0)+
    ggrepel::geom_text_repel(aes(label = round(amount_spent_inr_crores,0)), color = "black", size = 1.5,  hjust = 1)+
  labs(x = "Financial_Year",
       y = "Amount Spent in INR Crores", 
       title = "CSR spend by private(NON PSU) and public(PSU) companies in India", 
       subtitle = "from 2014-2015 to 2020-202 (size of dots are proportional to number of Private and Public \n Companies, figures in blue represent no. of companies and figures in black represent INR crores)",
       caption = "Data:MCA,India", 
       fill = "Company Type") +
  theme(axis.text.x = element_text(angle = 90))

Impact of Covid19 Pandemic on the CSR Spend

The Covid19 severely impacted the businesses all over the world and India was no exception.The drastic reduction in CSR spend from FY19-FY20 to FY20-21 tells the pain caused by the pandemic. This also led to severe reduction in the corporate profitability and as a result, a larger number of companies didn’t spend any money on CSR in FY20-21. As we know by now that Job losses and fear of the pandemic lead to mass migration from cities to rural areas. One can only hope that widespread vaccination drives and signs of economic recovery will lead to v shaped recovery in the CSR spend as well.

final_psu_non_psu %>% 
  filter(fy %in% c("2019-2020","2020-2021")) %>% 
  ggplot(aes(x = fy, y = amount_spent_inr_crores, fill = psu_non_psu))+
  geom_col(position = "dodge", width =  0.5) +
  facet_wrap(~psu_non_psu, scales = "free_y")+
  
    geom_text(aes(label = round(amount_spent_inr_crores,0)), size = 2.5, color = "black", hjust = 0, vjust = 1, fontface = "bold")+
  labs(x = "Financial_Year",
       y = "Amount Spentin INR Crores", 
       title = "CSR spend by private(NON PSU) and public(PSU) companies ", 
       subtitle = "(FY19-20 vs FY20-21)",
       caption = "Data:MCA,India", 
       fill = "Company Type") +
  theme(axis.text.x = element_text(angle = 90))

final_psu_non_psu %>% 
  filter(fy %in% c("2019-2020","2020-2021")) %>% 
  ggplot(aes(x = fy, y = number_of_comapanies, fill = psu_non_psu))+
  geom_col(position = "dodge", width =  0.5) +
  facet_wrap(vars(psu_non_psu), scales ="free_y")+
  
    geom_text(aes(label = number_of_comapanies), size = 2.5, color = "black", hjust = 0, vjust = 1, fontface = "bold")+
  labs(x = "Financial_Year",
       y = "Number of Comapanies", 
       title = "Number of private(NON PSU) and public(PSU) companies engaged in CSR ", 
       subtitle = "(FY19-20 vs FY20-21)",
       caption = "Data:MCA,India", 
       fill = "Company Type") +
  theme(axis.text.x = element_text(angle = 90))